use crate::error::{new_err_info, AppErr, SqlxErr, EC_NO_DEVICE};
use serde::{Deserialize, Serialize};
use sqlx::Row;
use sqlx::{Executor, SqliteConnection};

use super::get_pool;

const CREATE_SQL: &'static str = r#"
CREATE TABLE IF NOT EXISTS tb_device (
    id INTEGER PRIMARY KEY AUTOINCREMENT, 
    name TEXT NOT NULL, 
    mac_addr TEXT NOT NULL, 
    location TEXT NOT NULL, 
    currency_code TEXT NOT NULL, 
    lang TEXT NOT NULL, 
    money INTEGER NOT NULL, 
    UNIQUE(mac_addr)
)
"#;

pub async fn init() -> Result<(), SqlxErr> {
    get_pool().execute(CREATE_SQL).await?;
    Ok(())
}

#[derive(Debug, Serialize, Deserialize)]
pub struct Device {
    pub id: i64,
    pub name: String,
    pub mac_addr: String,
    pub location: String,
    pub currency_code: String,
    pub lang: String,
    pub money: u32,
}

pub async fn login(mac_addr: &str) -> Result<Device, AppErr> {
    let mut tx = get_pool().begin().await?;

    let id = is_exists(&mut *tx, mac_addr).await?;

    let id = match id {
        Some(id) => id,
        None => {
            create_impl(
                &mut *tx, "unknown", mac_addr, "unknown", "unknown", "unknown", 100,
            )
            .await?
        }
    };

    let device = get_device_impl(&mut *tx, id).await?;

    tx.commit().await?;

    Ok(device)
}

pub async fn edit_device(device: &Device) -> Result<(), AppErr> {

    sqlx::query(r#"
        UPDATE tb_device SET 
            name = ?, 
            mac_addr = ?,
            location = ?,
            currency_code = ?,
            lang = ?,
            money = ?
        WHERE id = ?
    "#)
    .bind(&device.name)
    .bind(&device.mac_addr)
    .bind(&device.location)
    .bind(&device.currency_code)
    .bind(&device.lang)
    .bind(device.money)
    .bind(device.id)
    .execute( get_pool() )
    .await?;

    Ok(())
}

pub async fn query_devices() -> Result<Box<[Device]>, AppErr> {
    
    let rows = sqlx::query(
        r#"
        SELECT id, name, mac_addr, location, currency_code, lang, money FROM tb_device
    "#,
    )
    .fetch_all(get_pool())
    .await?;

    let mut vec = Vec::with_capacity(rows.len());

    for row in rows {
        let device = Device {
            id: row.get(0),
            name: row.get(1),
            mac_addr: row.get(2),
            location: row.get(3),
            currency_code: row.get(4),
            lang: row.get(5),
            money: row.get(6),
        };
        vec.push(device);
    }

    Ok(vec.into_boxed_slice())
}

pub async fn get_device(id: i64) -> Result<Device, AppErr> {
    let mut tx = get_pool().begin().await?;
    let device = get_device_impl(&mut tx, id).await?;
    tx.commit().await?;
    Ok(device)
}

async fn get_device_impl(tx: &mut SqliteConnection, id: i64) -> Result<Device, AppErr> {
    let row = sqlx::query(r#"
        SELECT id, name, mac_addr, location, currency_code, lang, money FROM tb_device WHERE id = ? LIMIT 1
    "#)
    .bind(id)
    .fetch_optional(tx)
    .await?
    .ok_or(new_err_info(EC_NO_DEVICE, "没有该设备"))?;

    let device = Device {
        id: row.get(0),
        name: row.get(1),
        mac_addr: row.get(2),
        location: row.get(3),
        currency_code: row.get(4),
        lang: row.get(5),
        money: row.get(6),
    };

    Ok(device)
}

async fn is_exists(tx: &mut SqliteConnection, mac_addr: &str) -> Result<Option<i64>, SqlxErr> {
    let row = sqlx::query(
        r#"
        SELECT id FROM tb_device WHERE mac_addr = ?
    "#,
    )
    .bind(mac_addr)
    .fetch_optional(tx)
    .await?;
    if let Some(row) = row {
        let id = row.get(0);
        return Ok(Some(id));
    }
    Ok(None)
}

async fn create_impl(
    tx: &mut SqliteConnection,
    name: &str,
    mac_addr: &str,
    location: &str,
    currency_code: &str,
    lang: &str,
    money: u32,
) -> Result<i64, SqlxErr> {
    let row = sqlx::query(
        r#"
        INSERT INTO tb_device (
            name, mac_addr,
            location, currency_code,
            lang, money
        ) VALUES (?, ?, ?, ?, ?, ?)
    "#,
    )
    .bind(name)
    .bind(mac_addr)
    .bind(location)
    .bind(currency_code)
    .bind(lang)
    .bind(money)
    .execute(tx)
    .await?;

    Ok(row.last_insert_rowid())
}
